﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;

using MySql.Data.MySqlClient;

namespace JA.Negocios
{
    public static partial class ModelBO
    {

        public static int getImovelXAreaComunsCount(int? idImovel, int? idAreaComum)
        {
            string where = "WHERE ";
            StringBuilder query = new StringBuilder();
            
            query.AppendLine("SELECT count(IdAreaComum) qtd ");
            query.AppendLine("FROM imovelxareacomum ");

            if (idImovel != null)
            {
                query.AppendLine(where + "IdImovel=?IdImovel ");
                where = "AND ";
            }
            if (idAreaComum != null)
            {
                query.AppendLine(where + "IdAreaComum=?IdAreaComum ");
                where = "AND ";
            }

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            if (idImovel != null) cmd.Parameters.AddWithValue("?IdImovel", idImovel);
            if (idAreaComum != null) cmd.Parameters.AddWithValue("?IdAreaComum", idAreaComum);

            MySqlDataReader reader = null;
            try
            {
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();

                int quantidade = 0;

                if (reader.Read())
                {
                    quantidade = reader.GetInt32(reader.GetOrdinal("qtd"));
                }
                reader.Close();

                return quantidade;
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }
        }
        public static void getImovelXAreaComuns(ImovelVO imovelVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("SELECT ixac.IdAreaComum, a.AreaComum ");
            query.AppendLine("FROM imovelxareacomum ixac");
            query.AppendLine("     LEFT JOIN areacomum a ON ixac.IdAreaComum = a.IdAreaComum ");
            query.AppendLine("WHERE ixac.IdImovel = ?IdImovel ");
            query.AppendLine("ORDER BY ixac.IdAreaComum ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdImovel", imovelVO.idImovel);

            MySqlDataReader reader = null;
            try
            {
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();

                imovelVO.areaComumVOs.Clear();

                while (reader.Read())
                {
                    AreaComumVO areaComumVO = AreaComumVO.CreateAreaComumVO();
                    areaComumVO.idAreaComum = reader.GetInt32(reader.GetOrdinal("IdAreaComum"));
                    areaComumVO.areaComum = reader.GetString(reader.GetOrdinal("AreaComum"));
                    imovelVO.areaComumVOs.Add(areaComumVO);
                }
                reader.Close();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }
        }

        public static void newImovelxAreaComuns(List<AreaComumVO> areaComumVOs, int idImovel)
        {
            foreach (AreaComumVO areaComumVO in areaComumVOs)
            {
                newImovelxAreaComum(areaComumVO, idImovel);
            }
        }
        public static void newImovelxAreaComum(AreaComumVO areaComumVO, int idImovel)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("INSERT INTO imovelxareacomum ");
            query.AppendLine(" (  IdAreaComum, IdImovel ) ");
            query.AppendLine("VALUES ( ?IdAreaComum, ?IdImovel ) ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();
            cmd.Parameters.AddWithValue("?IdAreaComum", areaComumVO.idAreaComum);
            cmd.Parameters.AddWithValue("?IdImovel", idImovel);

            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static void delImovelxAreaComuns(List<AreaComumVO> areaComumVOs, int idImovel)
        {
            foreach (AreaComumVO areaComumVO in areaComumVOs)
            {
                delImovelxAreaComum(areaComumVO, idImovel);
            }
        }
        public static void delImovelxAreaComum(AreaComumVO areaComumVO, int idImovel)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("DELETE FROM imovelxareacomum ");
            query.AppendLine("WHERE IdAreaComum=?IdAreaComum and IdImovel=?IdImovel ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdAreaComum", areaComumVO.idAreaComum);
            cmd.Parameters.AddWithValue("?IdImovel", idImovel);

            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }
    }
}
